package com.cdcas;

import com.cdcas.bean.UserBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MyMain {
    public static  final String DRIVER="com.mysql.cj.jdbc.Driver";
    public static  final String URL="jdbc:mysql://localhost:3306/db1920";
    public static  final String USERNAME="root";
    public static  final String PASSWORD="root";

    public static void main(String[] args) {

        try {
            Class.forName(DRIVER);
            Connection conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            System.out.println(conn);
            Statement st = conn.createStatement();
//            ResultSet rs = st.executeQuery("select * from user");
//            List<UserBean> userBeans = new ArrayList<>();
//            while (rs.next()) {
//                UserBean userBean= new UserBean();
//                int uid = rs.getInt("uid");
//                String name = rs.getString("name");
//                int age = rs.getInt("age");
//                Date birthday = rs.getDate("birthday");
//                float salary = rs.getFloat("salary");
//                String note = rs.getString("note");
//
//                userBean.setUid(uid);
//                userBean.setName(name);
//                userBean.setAge(age);
//                userBean.setBirthday(birthday);
//                userBean.setSalary(salary);
//                userBean.setNote(note);
////                System.out.println("uid=" + uid + " name=" + age + " age = " +age +
////                        " birthday = " + birthday +  " salary = " + salary + " note=" + note);
//                System.out.println(userBean);
//                userBeans.add(userBean);
//            }
//            System.out.println(userBeans);

            //数据修改
            //数据添加
//            String insertSql = "insert into user (name,age,birthday,salary,note) " +
//                    "values ('lisi1',21,'1999-01-01',4500,'baidu.com'),('lisi2',21,'1999-01-01',4500,'baidu.com')";
//            int reult = st.executeUpdate(insertSql);
//            System.out.println("insert result  = "  + reult);

            //修改数据
//            String sql = "update user set birthday='1998-01-10',salary=5500 where uid=5 ";
//            int result = st.executeUpdate(sql);
//            System.out.println("修改结果 =" + result);

            //使用预处理方式解决sql语法问题
//            String sql = "insert into user (name,age,birthday,salary,note) values (?,?,?,?,?)";
//            PreparedStatement ps = conn.prepareStatement(sql);
//            ps.setString(1,"Mr'Lee");
//            ps.setInt(2,18);
//            ps.setDate(3,new Date(new java.util.Date().getTime()));
//            ps.setFloat(4,4567);
//            ps.setString(5,"yahoo");
//            int result = ps.executeUpdate();
//            System.out.println("使用preparestatment  结果=" + result);

            //模糊且分页查询
            long currentPage = 1; 		// 当前所在页
            int lineSize = 2;
            String sql = "select * from user where name like ? limit ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,"%a%");
            ps.setObject(2,(currentPage-1)*lineSize);
            ps.setObject(3,lineSize);
            ResultSet rs= ps.executeQuery();
            while (rs.next()) {
                UserBean userBean= new UserBean();
                int uid = rs.getInt("uid");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                float salary = rs.getFloat("salary");
                String note = rs.getString("note");

                userBean.setUid(uid);
                userBean.setName(name);
                userBean.setAge(age);
                userBean.setBirthday(birthday);
                userBean.setSalary(salary);
                userBean.setNote(note);
//                System.out.println("uid=" + uid + " name=" + age + " age = " +age +
//                        " birthday = " + birthday +  " salary = " + salary + " note=" + note);
                System.out.println(userBean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
